/************************************************************************************/
/*  Program name CAPFNDIMP_USER_RESP.sql                                            */
/*  Creater:  NIP Karen (PCCW - UniHub)                                             */
/*************   History   *************************                                */
/* 20 JAN, 2006   NIP Karen         First created                                   */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDIMP_USER_RESP';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_FND_USER_RESP;

	--lv_App_Short_Name		VARCHAR2;
	--lv_Security_Group_Key	VARCHAR2;
	lv_App_Short_Name		FND_APPLICATION_VL.APPLICATION_SHORT_NAME%TYPE;
	lv_Security_Group_Key	FND_SECURITY_GROUPS.SECURITY_GROUP_KEY%type;

BEGIN

	FOR C1 IN Cur_F LOOP
		BEGIN
			SELECT	APPLICATION_SHORT_NAME
			INTO	lv_App_Short_Name
			FROM	FND_APPLICATION_VL
			WHERE	APPLICATION_NAME	=	C1.Resp_Application_Name;

			SELECT	SECURITY_GROUP_KEY
			INTO	lv_Security_Group_Key
			FROM	FND_SECURITY_GROUPS
			WHERE	SECURITY_GROUP_ID	=	C1.Security_Group_ID;

			FND_USER_PKG.AddResp(
				UserName		=>	C1.User_Name,
				Resp_App		=>	lv_App_Short_Name,
				Resp_Key		=>	C1.Responsibility_Name,
				Security_Group	=>	lv_Security_Group_Key,
				--Description		=>	C1.Description,
				Description		=>	C1.resp_application_name,
				Start_Date		=>	C1.Start_Date,
				End_Date		=>	C1.End_Date
			);

		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/